package com.xcyy.zjdxmjk.zjdxmjk.repository;

import com.xcyy.zjdxmjk.zjdxmjk.entity.Town;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface TownRepository extends JpaRepository<Town,String> {
    Town save(Town town);
    List<Town> findByCodeStartsWith(String code);

    @Query(value = "SELECT * from town t where t.name like %?1%",nativeQuery = true)
    List<Town> findByName(String name);

    @Query(value = "SELECT sum(t.zjd_count) as zjd,sum(t.jsyd_count) as jsyd from town t",nativeQuery = true)
    Object countZjdAndJsyd();

    @Query(value = "SELECT sum(t.dcjggs),count(1) from town t",nativeQuery = true)
    Object countDcjggs();

    @Query(value = "SELECT sum(t.dcjggs),sum(t.cun_count) from town t where t.code like ?1%",nativeQuery = true)
    Object countDcjggsGroupByCode(String code);

    @Query(value = "SELECT t.dept,sum(t.zjd_count),SUM(t.jsyd_count) from town t GROUP BY t.dept ORDER BY FIELD(t.dept,'基础测绘一部','基础测绘二部','基础测绘三部','基础测绘四部','基础测绘五部','基础测绘六部','应急测绘部','测绘信息部','智慧城市部','测绘事业一部','测绘事业三部','测绘事业四部') ",nativeQuery = true)
    List<Object> countDept();

    @Query(value = "SELECT t.code from town t WHERE dept = ?1",nativeQuery = true)
    List<String> countDeptCode(String dept);

    List<Town> findByDept(String dept);

    Town findByCode(String code);

    @Query(value = "select CONCAT(LEFT(t.code,6),\"0000000000000\") s, sum(t.zjd_count),sum(t.jsyd_count) from town t GROUP BY s",nativeQuery = true)
    List<Object> countZjdAndJsydGroupByCountry();

    @Query(value = "select CONCAT(LEFT(t.code,4),\"000000000000000\") s, sum(t.zjd_count),sum(t.jsyd_count) from town t GROUP BY s",nativeQuery = true)
    List<Object> countZjdAndJsydGroupByCity();


}
